/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Sidang;
import Entity.TA;
import Entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import library.Koneksi;

/**
 *
 * @author mr2
 */
public class SidangTAModel {
    
    Connection koneksi;
    User user;
    Sidang sidang;
    TA ta;
    
    public SidangTAModel(){
        koneksi = Koneksi.getKoneksi();
    }
    
    public void DaftarSidangProposal(TA ta){
        
        PreparedStatement prepare = null;
        try{
            String sql = "INSERT INTO ta_proposal (id_ta) values(?)";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setInt(1, ta.getIdTA());
            
            prepare.executeUpdate();
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }      
    }
    
    public void DaftarSidangFinal(TA ta){
        PreparedStatement prepare = null;
        try{            
            
            String sql = "INSERT INTO ta_final (id_ta) values(?)";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setInt(1, ta.getIdTA());
            
            prepare.executeUpdate();
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
    //Model menampilkan daftar pendaftar sidang pada akun koorTA
    public List<Sidang> ListPendaftarSidang(String jenisSidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        List<Sidang> listPendaftar = new ArrayList<Sidang>();
        String sql = null;
        
        
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                try{
                    
                    sql = "SELECT id_proposal, ta_proposal.id_ta, ta.id_user, user.nama_user, ta.nama_lab, status_daftar_proposal "
                    + "FROM ta_proposal, ta, user "
                    + "WHERE ta.id_ta = ta_proposal.id_ta and ta.status_ta = '1' "
                    + "AND status_sidang_proposal = 'BELUM' AND ta.id_user = user.id_user ";
                
                    prepare = koneksi.prepareStatement(sql);
                    result = prepare.executeQuery();

                    while(result.next()){

                        User user = new User();
                        user.setIdUser(result.getString("id_user"));
                        user.setNamaUser(result.getString("nama_user"));
                        
                        TA ta = new TA();
                        ta.setUser(user);
                        ta.setNamaLab(result.getString("nama_lab"));
                        ta.setIdTA(result.getInt("id_ta"));
                        
                        Sidang sidang = new Sidang();
                        sidang.setTa(ta);
                        sidang.setIdSidang(result.getInt("id_proposal"));
                        sidang.setStatusDaftarSidang(result.getString("status_daftar_proposal"));

                        listPendaftar.add(sidang);
                    }
                    
                    return listPendaftar;
                    
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                    return listPendaftar;
                }finally{
                    if(prepare != null){
                        try{
                            prepare.close();
                        }catch(SQLException ex){
                            System.out.println(ex.getMessage());
                        }
                    }
                    if(result != null){
                        try{
                            result.close();
                        }catch(SQLException ex){
                            System.out.println(ex.getMessage());
                        }
                    }
                }
            }else{
                try{  
                    sql = "SELECT id_final, ta_final.id_ta, ta.id_user, user.nama_user, ta.nama_lab, status_daftar_final "
                    + "FROM ta, ta_final, user "
                    + "WHERE ta.id_ta = ta_final.id_ta and ta.status_ta = '1' "
                    + "AND status_sidang_final = 'BELUM' AND ta.id_user = user.id_user ";

                    prepare = koneksi.prepareStatement(sql);
                    result = prepare.executeQuery();

                    while(result.next()){
                        
                        User user = new User();
                        user.setIdUser(result.getString("id_user"));
                        user.setNamaUser(result.getString("nama_user"));
                        
                        TA ta = new TA();
                        ta.setUser(user);
                        ta.setNamaLab(result.getString("nama_lab"));
                        ta.setIdTA(result.getInt("id_ta"));
                        
                        Sidang sidang = new Sidang();
                        sidang.setTa(ta);
                        sidang.setIdSidang(result.getInt("id_final"));
                        sidang.setStatusDaftarSidang(result.getString("status_daftar_final"));

                        listPendaftar.add(sidang);
                    }
                   return listPendaftar; 
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                    return listPendaftar;
                }finally{
                    if(prepare != null){
                        try{
                            prepare.close();
                        }catch(SQLException ex){
                            System.out.println(ex.getMessage());
                        }
                    }
                    if(result != null){
                        try{
                            result.close();
                        }catch(SQLException ex){
                            System.out.println(ex.getMessage());
                        }
                    }
                } 
            }
    }
    
    public void persetujuanPendaftar(Sidang sidang){
        
        PreparedStatement prepare = null; 
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
        
        try{            

            if("SIDANG PROPOSAL".equals(jenisSidang)){

                sql = "update ta_proposal set status_daftar_proposal = ? where id_proposal = ? ";

//            }else if("SIDANG PROGRESS".equals(jenisSidang)){
//
//                sql = "update ta_progress set status_daftar_progress = ? where id_progress = ? ";

            }else{

                sql = "update ta_final set status_daftar_final = ? where id_final = ? ";

            }
//          + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";

            prepare = koneksi.prepareStatement(sql);

            prepare.setString(1, sidang.getStatusDaftarSidang());
            prepare.setInt(2, sidang.getIdSidang());

            prepare.executeUpdate();

        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){

                }
            }
        }
    }
    
    public List<Sidang> RekapPendaftarFinal(TA ta, String periode){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Sidang> rekapJadwalFinal = new ArrayList<Sidang>();
        
        
        String lab = ta.getNamaLab();
        String[] token = periode.split(",");
        String periodeAwal = token[0];
        String periodeAkhir = token[1];
        
        
        
        String sql = null;
        try{
            sql = "SELECT user.nama_user, ta.id_user, ta.judul, ta.pembimbing_1, ta.pembimbing_2, ta.pembimbing_luar, ta.nama_lab, final_tgl_sidang,  final_waktu_sidang, final_tempat_sidang, final_penguji1, final_penguji2 "
                + "FROM ta_final, ta, user "
                + "WHERE ta_final.status_daftar_final = 'OK' AND final_tgl_sidang is not null AND user.id_user = ta.id_user AND ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' AND status_sidang_final = 'BELUM' "
                + "AND ta.nama_lab = ? AND ta_final.final_tgl_sidang >= ? AND ta_final.final_tgl_sidang <= ? "
                + "ORDER BY ta.id_user ";

            /*kondisi cadangan
                * 1. AND ta_proposal.status_sidang_proposal <> 'DITERIMA' 
            */
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, lab);
            prepare.setString(2, periodeAwal);
            prepare.setString(3, periodeAkhir);
            result = prepare.executeQuery();

            while(result.next()){

                this.user = new User();
                user.setIdUser(result.getString("id_user"));
                user.setNamaUser(result.getString("nama_user"));

                this.ta = new TA();
                ta.setUser(user);
                ta.setJudul(result.getString("judul"));
                ta.setNamaLab(result.getString("nama_lab"));
                ta.setPembimbing1(result.getString("pembimbing_1"));
                ta.setPembimbing2(result.getString("pembimbing_2"));
                ta.setPembimbingLuar(result.getString("pembimbing_luar"));

                Sidang sidangta = new Sidang();
                sidangta.setTa(ta);
                sidangta.setTglSidang(result.getString("final_tgl_sidang"));
                sidangta.setWktSidang(result.getString("final_waktu_sidang"));
                sidangta.setTempatSidang(result.getString("final_tempat_sidang"));
                sidangta.setPenguji1(result.getString("final_penguji1"));
                sidangta.setPenguji2(result.getString("final_penguji2"));
                rekapJadwalFinal.add(sidangta);
            }
            return rekapJadwalFinal;

        }catch(SQLException ex){          
            System.out.println(ex.getMessage());
            return rekapJadwalFinal;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
    }
    
    public Sidang getDetailSidang(Sidang sidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
        
        try{
            
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                sql = "SELECT id_proposal, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, "
                    + "proposal_penguji1, proposal_penguji2, status_sidang_proposal "
                    + "FROM ta_proposal, ta "
                    + "WHERE ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' " 
                    + "AND ta.id_ta = ? "
                    + "ORDER BY id_proposal desc "
                    + "limit 1";
            //+ "AND status_sidang_proposal <> 'SIDANG ULANG' AND status_sidang_proposal <> 'TUGAS AKHIR DIBATALKAN' "
    
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getTa().getIdTA());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_proposal"));
                    sidang.setTglSidang(result.getString("proposal_tgl_sidang"));
                    sidang.setWktSidang(result.getString("proposal_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("proposal_tempat_sidang"));
                    sidang.setPenguji1(result.getString("proposal_penguji1"));
                    sidang.setPenguji2(result.getString("proposal_penguji2"));
//                    sidang.setTglDiterima(result.getString("proposal_tgl_diterima"));
                    sidang.setStatusSidang(result.getString("status_sidang_proposal"));
                    
                }
            }else{
                
                sql = "SELECT id_final, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, "
                    + "final_penguji1, final_penguji2, status_sidang_final, "
                    + "nilai_pembimbing1, nilai_pembimbing2, nilai_penguji1, nilai_penguji2, nilai_angka, nilai_huruf "
                    + "FROM ta_final, ta "
                    + "WHERE ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' "
                    + "AND ta.id_ta = ? "
                    + "ORDER BY id_final desc "
                    + "limit 1";
            //+ "AND status_sidang_final <> 'SIDANG ULANG' AND status_sidang_final <> 'TUGAS AKHIR DIBATALKAN' "
                
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getTa().getIdTA());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_final"));
                    sidang.setTglSidang(result.getString("final_tgl_sidang"));
                    sidang.setWktSidang(result.getString("final_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("final_tempat_sidang"));
                    sidang.setPenguji1(result.getString("final_penguji1"));
                    sidang.setPenguji2(result.getString("final_penguji2"));
                    sidang.setStatusSidang(result.getString("status_sidang_final"));
                    sidang.setNilaiPembimbing1(result.getInt("nilai_pembimbing1"));
                    sidang.setNilaiPembimbing2(result.getInt("nilai_pembimbing2"));
                    sidang.setNilaiPenguji1(result.getInt("nilai_penguji1"));
                    sidang.setNilaiPenguji2(result.getInt("nilai_penguji2"));
                    sidang.setNilaiAngka(result.getInt("nilai_angka"));
                    sidang.setNilaiHuruf(result.getString("nilai_huruf"));
                }
            }
            return sidang;
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){

                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return sidang;
    }
    
    //Mendapatkan status pendaftaran proposal
    public String getStatusDaftarProposal(TA ta){
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String statusDaftarProposal = "";
        
        try {
               
            String sql = "select status_daftar_proposal "
                       + "from ta_proposal, ta "
                       + "where ta.id_ta = ta_proposal.id_ta and status_sidang_proposal <> 'SIDANG ULANG' "
                       + "and ta.status_ta = '1' and ta.id_ta = ? ";
//                            + "and pendaftaran_proposal.id_daftar_proposal = sidang_proposal.id_daftar_proposal "
//                            + "and status_sidang_proposal <> 'SIDANG ULANG' and status_sidang_proposal <> 'SIDANG ULANG' "
//                            + "and ta.id_user = ? ";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){                                                     
               statusDaftarProposal = "SUDAH DAFTAR PROPOSAL";                       
            }else{
               statusDaftarProposal = "BELUM DAFTAR PROPOSAL";
            }
                    
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return statusDaftarProposal;
    }
    
    //Mendapatkan status pendaftaran progress
//    public String getStatusDaftarProgress(TA ta){
//        
//        PreparedStatement prepare = null;
//        ResultSet result = null;
//       
//        String statusDaftarProgress = "";
//        
//        try {
//               
//            String sql = "select status_daftar_progress "
//                       + "from ta_progress, ta "
//                       + "where ta.id_ta = ta_progress.id_ta and status_sidang_progress <> 'SIDANG ULANG'  "
//                       + "and ta.status_ta = '1' and ta.id_ta = ? ";
//            
//            prepare = koneksi.prepareStatement(sql);
//            prepare.setInt(1, ta.getIdTA());
//            result = prepare.executeQuery();
//            
//            if(result.next()){                                                     
//               statusDaftarProgress = "SUDAH DAFTAR PROGRESS";
//            }else{
//               statusDaftarProgress = "BELUM DAFTAR PROGRESS";
//            }
//                    
//        } catch (SQLException ex) {
//            return ex.getMessage();
//        }finally{
//            if(prepare != null){
//                try{
//                    prepare.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//            if(result != null){
//                try{
//                    result.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//        }
//        return statusDaftarProgress;
//
//    }
    
    //Mendapatkan status pendaftaran final
    public String getStatusDaftarFinal(TA ta){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String statusDaftarFinal = "";
        
        try {
               
            String sql = "select id_final "
                       + "from ta_final, ta "
                       + "where ta.id_ta = ta_final.id_ta and status_sidang_final <> 'SIDANG ULANG' "
                       + "and ta.status_ta = '1' and  ta.id_ta = ? ";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){                                                     
               statusDaftarFinal = "SUDAH DAFTAR FINAL";
            }else{
               statusDaftarFinal = "BELUM DAFTAR FINAL";
            }
                    
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return statusDaftarFinal;        
    }
    
    //Mendapatkan status sidang proposal
    public String getStatusSidangProposal(TA ta){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String statusSidangProposal = "";
        
        try {
               
            String sql = "SELECT status_sidang_proposal "
                       + "FROM ta_proposal, ta "
                       + "WHERE ta.id_TA = ta_proposal.id_TA "
                       + "and ta.status_ta = '1' and ta.id_ta = ? "
                       + "order by id_proposal desc "
                       + "limit 1";
//                       + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){
                statusSidangProposal = result.getString("status_sidang_proposal");                                                      
            }else{
                statusSidangProposal = "BELUM";
            }                                                                
                       
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return statusSidangProposal;
    }
    
    //Mendapatkan status sidang progress
//    public String getStatusSidangProgress(TA ta){
//        
//        PreparedStatement prepare = null;
//        ResultSet result = null;
//       
//        String statusSidangPrgress = "";
//        
//        try {
//               
//            String sql = "SELECT status_sidang_progress "
//                       + "FROM ta_progress, ta "
//                       + "WHERE ta.id_TA = ta_progress.id_TA "
//                       + "and ta.status_ta = '1' and ta.id_ta = ? "
//                       + "order by id_progress desc "
//                       + "limit 1 ";
////                       + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";
//            
//            prepare = koneksi.prepareStatement(sql);
//            prepare.setInt(1, ta.getIdTA());
//            result = prepare.executeQuery();
//            
//            if(result.next()){
//                statusSidangPrgress = result.getString("status_sidang_progress");                                                      
//            }else{
//                statusSidangPrgress = "BELUM";
//            }
//            
//        } catch (SQLException ex) {
//            return ex.getMessage();
//        }finally{
//            if(prepare != null){
//                try{
//                    prepare.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//            if(result != null){
//                try{
//                    result.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//        }
//        return statusSidangPrgress;
//    }
    
    //Mendapatkan Status Sidang Final
    public String getStatusSidangFinal(TA ta){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String statusSidangFinal = "";
        
        try {
               
            String sql = "SELECT status_sidang_final "
                       + "FROM ta_final, ta "
                       + "WHERE ta.id_TA = ta_final.id_TA "
                       + "and ta.status_ta = '1' and ta.id_ta = ? "
                       + "order by id_final desc "
                       + "limit 1";
//                       + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){
                statusSidangFinal = result.getString("status_sidang_final") ;                                                      
            }else{
                statusSidangFinal = "BELUM";
            }
            
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return statusSidangFinal;
    }
    
    public String getTglProposalDiterima(TA ta){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String tglProposalDiterima = null;
        
        try {
               
            String sql = "SELECT proposal_tgl_sidang "
                       + "FROM ta_proposal, ta "
                       + "WHERE ta.id_TA = ta_proposal.id_TA AND (ta_proposal.status_sidang_proposal = 'DITERIMA' OR ta_proposal.status_sidang_proposal = 'DITERIMA DENGAN REVISI')"
                       + "and ta.status_ta = '1' and ta.id_ta = ? "
                       + "order by id_proposal desc "
                       + "limit 1";
//                       + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){
                tglProposalDiterima = result.getString("proposal_tgl_sidang") ;                                                      
            }
            
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return tglProposalDiterima;
    }
    
    public String getTglFinalDiterima(TA ta){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
       
        String finalTglDiterima = null;
        
        try {
               
            String sql = "SELECT final_tgl_sidang "
                       + "FROM ta_final, ta "
                       + "WHERE ta.id_TA = ta_final.id_TA AND (ta_final.status_sidang_final = 'LULUS' OR ta_final.status_sidang_final = 'LULUS DENGAN REVISI') "
                       + "and ta.status_ta = '1' and ta.id_ta = ? "
                       + "order by id_final desc "
                       + "limit 1";
//                       + "AND (proposal_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND progress_status_sidang <> 'TUGAS AKHIR DIBATALKAN' AND final_status_sidang <> 'TUGAS AKHIR DIBATALKAN')";
            prepare = koneksi.prepareStatement(sql);
            prepare.setInt(1, ta.getIdTA());
            result = prepare.executeQuery();
            
            if(result.next()){
                finalTglDiterima = result.getString("final_tgl_sidang") ;                                                      
            }
            
        } catch (SQLException ex) {
            return ex.getMessage();
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return finalTglDiterima;
    }
    
    
    public double statusLamaPengerjaan(TA ta, String tglProposal, String tglFinal){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        double lamaPengerjaan = 0;
        
        if(tglFinal == null){
            try{
                String sql = "select ((datediff( curdate(), ? )))/30 as lamaPengerjaan from ta, ta_proposal "
                        + "where ta.id_ta = ta_proposal.id_ta and ta.id_ta = ? ";
                prepare = koneksi.prepareStatement(sql);
                prepare.setString(1, tglProposal);
                prepare.setInt(2, ta.getIdTA());
                result = prepare.executeQuery();
                
                if(result.next()){
                    lamaPengerjaan = result.getDouble("lamaPengerjaan");
                }else{
                    lamaPengerjaan = 0;
                }
            }catch(SQLException ex){
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
            
            
            
        }else{
                try{
                String sql = "select ((datediff( ? , ? )))/30 as lamaPengerjaan from ta, ta_proposal, ta_final "
                        + "where ta.id_ta = ta_proposal.id_ta and ta.id_ta = ta_final.id_ta and ta.id_ta = ? ";
                prepare = koneksi.prepareStatement(sql);
                prepare.setString(1, tglFinal);
                prepare.setString(2, tglProposal);
                prepare.setInt(3, ta.getIdTA());
                result = prepare.executeQuery();
                
                if(result.next()){
                    lamaPengerjaan = result.getDouble("lamaPengerjaan");
                }else{
                    lamaPengerjaan = 0;
                }
            }catch(SQLException ex){
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
            
        }
        return lamaPengerjaan;
    }
    
    public int statusJlhBimbingan(TA ta){
       
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        int jlhBimbingan = 0;
        
        try{
                String sql = "SELECT COUNT( bimbingan_ta.id_bim ) as jlhBimbingan FROM ta, bimbingan_ta "
                        + "WHERE ta.id_ta = bimbingan_ta.id_ta AND status_bim = 'OK' AND ta.id_ta = ? "
                        + "GROUP BY ta.id_ta ";
                prepare = koneksi.prepareStatement(sql);
                prepare.setInt(1, ta.getIdTA());
                result = prepare.executeQuery();
                
                if(result.next()){
                    jlhBimbingan = result.getInt("jlhBimbingan");
                }else{
                    jlhBimbingan = 0;
                }
            }catch(SQLException ex){
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
            return jlhBimbingan;
    }
    
    public List<Sidang> ListJadwalSidang(Sidang sidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Sidang> listJadwalSidang = new ArrayList<Sidang>();
        
        String jenisSidang = sidang.getJenisSidang();
        String TGLSidang = sidang.getTglSidang();
        String namaLab = sidang.getTa().getNamaLab();
        String sql = null;

            
            try{
                
                if("SIDANG PROPOSAL".equals(jenisSidang)){
                    
                    sql = "SELECT id_proposal, ta_proposal.id_ta, ta.id_user, user.nama_user, ta.judul, ta.nama_lab, ta.pembimbing_1, ta.pembimbing_2, id_proposal, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, proposal_penguji1, proposal_penguji2 "
                        + "FROM ta_proposal, ta, user "
                        + "WHERE ta_proposal.status_daftar_proposal = 'OK' AND ta_proposal.status_sidang_proposal = 'BELUM' "
                        + "AND ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? and ta.id_user = user.id_user "
                        + "ORDER by proposal_tgl_sidang";
                    
                    if("".equals(TGLSidang)){
//                     
                        prepare = koneksi.prepareStatement(sql);
                        
                        prepare.setString(1, namaLab);
                        result = prepare.executeQuery();
                    }else{
                        
                        sql = sql + " and ta_proposal.proposal_tgl_sidang = ? ";
                        
                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, namaLab);
                        prepare.setString(2, TGLSidang);
                        result = prepare.executeQuery();
                    }
                    
                    while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            user.setNamaUser(result.getString("nama_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));
                            ta.setJudul(result.getString("judul"));
                            ta.setNamaLab(result.getString("nama_lab"));
                            ta.setPembimbing1(result.getString("pembimbing_1"));
                            ta.setPembimbing2(result.getString("pembimbing_2"));
                            
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_proposal"));
                            sidangta.setWktSidang(result.getString("proposal_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("proposal_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("proposal_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("proposal_penguji1"));
                            sidangta.setPenguji2(result.getString("proposal_penguji2"));
                            listJadwalSidang.add(sidangta);
                        }
                        return listJadwalSidang;

                }else{
                        
                        sql = "SELECT id_final, ta_final.id_ta, ta.id_user, user.nama_user, ta.judul, ta.nama_lab, ta.pembimbing_1, ta.pembimbing_2, id_final, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, final_penguji1, final_penguji2 "
                        + "FROM ta_final, ta, user "
                        + "WHERE ta_final.status_daftar_final = 'OK' AND ta_final.status_sidang_final = 'BELUM' "
                        + "AND ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? AND ta.id_user = user.id_user ";
                        
                        if("".equals(TGLSidang)){
                            prepare = koneksi.prepareStatement(sql);

                            prepare.setString(1, namaLab);
                            result = prepare.executeQuery();
                        }else{
                            
                            sql = sql + "and ta_final.final_tgl_sidang = ?"; 
                            
                            prepare = koneksi.prepareStatement(sql);

                            prepare.setString(1, namaLab);
                            prepare.setString(2, TGLSidang);
                            result = prepare.executeQuery();
                        }
                        
                        while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            user.setNamaUser(result.getString("nama_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));
                            ta.setJudul(result.getString("judul"));
                            ta.setNamaLab(result.getString("nama_lab"));
                            ta.setPembimbing1(result.getString("pembimbing_1"));
                            ta.setPembimbing2(result.getString("pembimbing_2"));
                            
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_final"));
                            sidangta.setWktSidang(result.getString("final_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("final_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("final_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("final_penguji1"));
                            sidangta.setPenguji2(result.getString("final_penguji2"));
                            listJadwalSidang.add(sidangta);
                        }
                }
                
            }catch(SQLException ex){          
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
        return listJadwalSidang;
    }
    
    public Sidang getDetailJadwalSidang(Sidang sidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
        
        try{
            
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                sql = "SELECT id_proposal, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, "
                    + "proposal_penguji1, proposal_penguji2 "
                    + "FROM ta_proposal, ta "
                    + "WHERE ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' AND id_proposal = ? ";
            
    
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getIdSidang());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_proposal"));
                    sidang.setTglSidang(result.getString("proposal_tgl_sidang"));
                    sidang.setWktSidang(result.getString("proposal_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("proposal_tempat_sidang"));
                    sidang.setPenguji1(result.getString("proposal_penguji1"));
                    sidang.setPenguji2(result.getString("proposal_penguji2"));
                }
                
//            }else if("SIDANG PROGRESS".equals(jenisSidang)){
//                
//                sql = "SELECT id_progress, progress_tgl_sidang, progress_waktu_sidang, progress_tempat_sidang, "
//                    + "progress_penguji1, progress_penguji2 "
//                    + "FROM ta_progress, ta "
//                    + "WHERE ta.id_ta = ta_progress.id_ta AND ta.status_ta = '1' AND id_progress = ? ";
//            
//    
//                prepare = koneksi.prepareStatement(sql);
//
//                prepare.setInt(1, sidang.getIdSidang());
//
//                result = prepare.executeQuery();
//
//                if(result.next()){
//
//                    sidang.setIdSidang(result.getInt("id_progress"));
//                    sidang.setTglSidang(result.getString("progress_tgl_sidang"));
//                    sidang.setWktSidang(result.getString("progress_waktu_sidang"));
//                    sidang.setTempatSidang(result.getString("progress_tempat_sidang"));
//                    sidang.setPenguji1(result.getString("progress_penguji1"));
//                    sidang.setPenguji2(result.getString("progress_penguji2"));
//                }
//                
            }else{
                
                sql = "SELECT id_final, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, "
                    + "final_penguji1, final_penguji2 "
                    + "FROM ta_final, ta "
                    + "WHERE ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' AND id_final = ? ";
            
    
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getIdSidang());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_final"));
                    sidang.setTglSidang(result.getString("final_tgl_sidang"));
                    sidang.setWktSidang(result.getString("final_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("final_tempat_sidang"));
                    sidang.setPenguji1(result.getString("final_penguji1"));
                    sidang.setPenguji2(result.getString("final_penguji2"));
                }
            }
            return sidang;
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){

                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return sidang;
    }
    
    public boolean PenjadwalanSidang(Sidang sidang){
        
        boolean hasil = true;      
        
        PreparedStatement prepare = null;
        
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
        
        try{
            
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                sql = "update ta_proposal set proposal_tgl_sidang = ?, proposal_waktu_sidang = ?, proposal_tempat_sidang = ?, "
                        + "proposal_penguji1 = ?, proposal_penguji2 = ? "
                        + "where id_proposal = ? ";
                
//            }else if("SIDANG PROGRESS".equals(jenisSidang)){
//                
//                sql = "update ta_progress set progress_tgl_sidang = ?, progress_waktu_sidang = ?, progress_tempat_sidang = ?, "
//                        + "progress_penguji1 = ?, progress_penguji2 = ? "
//                        + "where id_progress = ? ";
//                
            }else{
                
                sql = "update ta_final set final_tgl_sidang = ?, final_waktu_sidang = ?, final_tempat_sidang = ?, "
                        + "final_penguji1 = ?, final_penguji2 = ? "
                        + "where id_final = ? ";
                
            }
            
            prepare = koneksi.prepareStatement(sql);

            prepare.setString(1, sidang.getTglSidang());
            prepare.setString(2, sidang.getWktSidang());
            prepare.setString(3, sidang.getTempatSidang());
            prepare.setString(4, sidang.getPenguji1());
            prepare.setString(5, sidang.getPenguji2());
            prepare.setInt(6, sidang.getIdSidang());

            int status = prepare.executeUpdate();
            if(status > 0){
                hasil = true;
            }else{
                hasil = false;
            }
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
            hasil = false;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){                   
                }
            }
        }
        return hasil;
    }
    
    public List<Sidang> ListBeritaAcara(Sidang sidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Sidang> listBeritaAcara = new ArrayList<Sidang>();
        
        String jenisSidang = sidang.getJenisSidang();
        String TGLSidang = sidang.getTglSidang();
        String namaLab = sidang.getTa().getNamaLab();
        String sql = null;
 
            try{
                
                if("SIDANG PROPOSAL".equals(jenisSidang)){
                    
                    if("".equals(TGLSidang)){
                        
                        sql = "SELECT id_proposal, ta_proposal.id_ta, ta.id_user, user.nama_user, id_proposal, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, "
                            + "proposal_penguji1, proposal_penguji2, status_sidang_proposal "
                        + "FROM ta_proposal, ta, user "
                        + "WHERE ta_proposal.status_daftar_proposal = 'OK' AND proposal_tgl_sidang is not null "
                        + "AND ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? AND ta.id_user = user.id_user "
                        + "ORDER BY status_sidang_proposal";
                        
                        /*kondisi cadangan
                         * 1. AND ta_proposal.status_sidang_proposal <> 'DITERIMA' 
                        */
                    
                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, namaLab);
                        result = prepare.executeQuery();
                        
                        while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            user.setNamaUser(result.getString("nama_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));
                               
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_proposal"));
                            sidangta.setWktSidang(result.getString("proposal_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("proposal_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("proposal_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("proposal_penguji1"));
                            sidangta.setPenguji2(result.getString("proposal_penguji2"));
                            sidangta.setStatusSidang(result.getString("status_sidang_proposal"));
                            listBeritaAcara.add(sidangta);
                        }
                        return listBeritaAcara;
                        
                    }else{
                        
                        sql = "SELECT id_proposal, ta_proposal.id_ta, ta.id_user, user.nama_user, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, "
                            + "proposal_penguji1, proposal_penguji2, status_sidang_proposal "
                        + "FROM ta_proposal, ta, user "
                        + "WHERE ta_proposal.status_daftar_proposal = 'OK' AND proposal_tgl_sidang is not null "
                        + "AND ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? and ta_proposal.proposal_tgl_sidang = ? AND ta.id_user = user.id_user "
                        + "ORDER BY status_sidang_proposal ";
                        
                        /*kondisi cadangan
                         * 1. AND ta_proposal.status_sidang_proposal <> 'DITERIMA' 
                        */
                    
                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, namaLab);
                        prepare.setString(2, TGLSidang);
                        result = prepare.executeQuery();
                        
                        while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            user.setNamaUser(result.getString("nama_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));                           
                            
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_proposal"));
                            sidangta.setWktSidang(result.getString("proposal_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("proposal_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("proposal_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("proposal_penguji1"));
                            sidangta.setPenguji2(result.getString("proposal_penguji2"));
                            sidangta.setStatusSidang(result.getString("status_sidang_proposal"));
                            listBeritaAcara.add(sidangta);
                        }
                        return listBeritaAcara;
                        
                    }
                    
//                }else if("SIDANG PROGRESS".equals(jenisSidang)){
//                    
//                    if("".equals(TGLSidang)){
//                        
//                        sql = "SELECT id_progress, ta_progress.id_ta, ta.id_user, id_progress, progress_tgl_sidang, progress_waktu_sidang, progress_tempat_sidang, "
//                            + "progress_penguji1, progress_penguji2, status_sidang_progress "
//                        + "FROM ta_progress, ta "
//                        + "WHERE ta_progress.status_daftar_progress = 'OK' AND progress_tgl_sidang is not null "
//                        + "AND ta.id_ta = ta_progress.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? "
//                        + "ORDER BY status_sidang_progress ";
//                        
//                        /*kondisi cadangan
//                         * 1. AND ta_progress.status_sidang_progress <> 'DITERIMA' 
//                        */
//                        prepare = koneksi.prepareStatement(sql);
//
//                        prepare.setString(1, namaLab);
//                        result = prepare.executeQuery();
//                        
//                        while(result.next()){
//                            
//                            User user = new User();
//                            user.setIdUser(result.getString("id_user"));
//                            
//                            TA ta = new TA();
//                            ta.setUser(user);
//                            ta.setIdTA(result.getInt("id_ta"));
//                              
//                            Sidang sidangta = new Sidang();
//                            sidangta.setTa(ta);
//                            sidangta.setIdSidang(result.getInt("id_progress"));
//                            sidangta.setWktSidang(result.getString("progress_waktu_sidang"));
//                            sidangta.setTglSidang(result.getString("progress_tgl_sidang"));
//                            sidangta.setTempatSidang(result.getString("progress_tempat_sidang"));
//                            sidangta.setPenguji1(result.getString("progress_penguji1"));
//                            sidangta.setPenguji2(result.getString("progress_penguji2"));
//                            sidangta.setStatusSidang(result.getString("status_sidang_progress"));
//                            listBeritaAcara.add(sidangta);
//                        }
//                        return listBeritaAcara;
//                        
//                    }else{
//                        sql = "SELECT id_progress, ta_progress.id_ta, ta.id_user, progress_tgl_sidang, progress_waktu_sidang, progress_tempat_sidang, "
//                                + "progress_penguji1, progress_penguji2, status_sidang_progress "
//                        + "FROM ta_progress, ta "
//                        + "WHERE ta_progress.status_daftar_progress = 'OK' AND progress_tgl_sidang is not null "
//                        + "AND ta.id_ta = ta_progress.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? and ta_progress.progress_tgl_sidang = ? "
//                        + "ORDER BY status_sidang_progress ";
//                        
//                        /*kondisi cadangan
//                         * 1. AND ta_progress.status_sidang_progress <> 'DITERIMA' 
//                        */
//                    
//                        prepare = koneksi.prepareStatement(sql);
//
//                        prepare.setString(1, namaLab);
//                        prepare.setString(2, TGLSidang);
//                        result = prepare.executeQuery();
//                        
//                        while(result.next()){
//                            
//                            User user = new User();
//                            user.setIdUser(result.getString("id_user"));
//                            
//                            TA ta = new TA();
//                            ta.setUser(user);
//                            ta.setIdTA(result.getInt("id_ta"));
//                              
//                            Sidang sidangta = new Sidang();
//                            sidangta.setTa(ta);
//                            sidangta.setIdSidang(result.getInt("id_progress"));
//                            sidangta.setWktSidang(result.getString("progress_waktu_sidang"));
//                            sidangta.setTglSidang(result.getString("progress_tgl_sidang"));
//                            sidangta.setTempatSidang(result.getString("progress_tempat_sidang"));
//                            sidangta.setPenguji1(result.getString("progress_penguji1"));
//                            sidangta.setPenguji2(result.getString("progress_penguji2"));
//                            sidangta.setStatusSidang(result.getString("status_sidang_progress"));
//                            listBeritaAcara.add(sidangta);
//                        }
//                        return listBeritaAcara;
//                        
//                    }

                }else{
                    
                    if("".equals(TGLSidang)){
                        
                        sql = "SELECT id_final, ta_final.id_ta, ta.id_user, id_final, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, "
                            + "final_penguji1, final_penguji2, status_sidang_final "
                        + "FROM ta_final, ta "
                        + "WHERE ta_final.status_daftar_final = 'OK' AND final_tgl_sidang is not null "
                        + "AND ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? "
                        + "ORDER BY status_sidang_final ";
                        
                        /*
                         * kondisi cadangan 
                         * 1. AND ta_final.status_sidang_final <> 'DITERIMA'
                         */
                    
                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, namaLab);
                        result = prepare.executeQuery();
                        
                        while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));                            
                            
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_final"));
                            sidangta.setWktSidang(result.getString("final_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("final_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("final_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("final_penguji1"));
                            sidangta.setPenguji2(result.getString("final_penguji2"));
                            sidangta.setStatusSidang(result.getString("status_sidang_final"));
                            listBeritaAcara.add(sidangta);
                        }
                        
                    }else{
                        
                        sql = "SELECT id_final, ta_final.id_ta, ta.id_user, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, "
                            + "final_penguji1, final_penguji2, status_sidang_final "
                        + "FROM ta_final, ta "
                        + "WHERE ta_final.status_daftar_final = 'OK' AND final_tgl_sidang is not null "
                        + "AND ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' and ta.nama_lab = ? and ta_final.final_tgl_sidang = ? "
                        + "ORDER BY status_sidang_final ";
                        
                        /*
                         * kondisi cadangan 
                         * 1. AND ta_final.status_sidang_final <> 'DITERIMA'
                         */
                    
                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, namaLab);
                        prepare.setString(2, TGLSidang);
                        result = prepare.executeQuery();
                        
                        while(result.next()){
                            
                            User user = new User();
                            user.setIdUser(result.getString("id_user"));
                            
                            TA ta = new TA();
                            ta.setUser(user);
                            ta.setIdTA(result.getInt("id_ta"));                           
                            
                            Sidang sidangta = new Sidang();
                            sidangta.setTa(ta);
                            sidangta.setIdSidang(result.getInt("id_final"));
                            sidangta.setWktSidang(result.getString("final_waktu_sidang"));
                            sidangta.setTglSidang(result.getString("final_tgl_sidang"));
                            sidangta.setTempatSidang(result.getString("final_tempat_sidang"));
                            sidangta.setPenguji1(result.getString("final_penguji1"));
                            sidangta.setPenguji2(result.getString("final_penguji2"));
                            sidangta.setStatusSidang(result.getString("status_sidang_final"));
                            listBeritaAcara.add(sidangta);
                        }
                        
                    }
                }
                
            }catch(SQLException ex){          
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
        return listBeritaAcara;
    }
    
    public Sidang getDetailBeritaAcara(Sidang sidang){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
        
        try{
            
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                sql = "SELECT id_proposal, proposal_tgl_sidang, proposal_waktu_sidang, proposal_tempat_sidang, "
                    + "proposal_penguji1, proposal_penguji2, status_sidang_proposal "
                    + "FROM ta_proposal, ta "
                    + "WHERE ta.id_ta = ta_proposal.id_ta AND ta.status_ta = '1' AND id_proposal = ? ";
            
    
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getIdSidang());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_proposal"));
                    sidang.setTglSidang(result.getString("proposal_tgl_sidang"));
                    sidang.setWktSidang(result.getString("proposal_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("proposal_tempat_sidang"));
                    sidang.setPenguji1(result.getString("proposal_penguji1"));
                    sidang.setPenguji2(result.getString("proposal_penguji2"));
//                    sidang.setTglDiterima(result.getString("proposal_tgl_diterima"));
                    sidang.setStatusSidang(result.getString("status_sidang_proposal"));
                    
                }
            }else{
                
                sql = "SELECT id_final, final_tgl_sidang, final_waktu_sidang, final_tempat_sidang, "
                    + "final_penguji1, final_penguji2, status_sidang_final, "
                    + "nilai_pembimbing1, nilai_pembimbing2, nilai_penguji1, nilai_penguji2, nilai_angka, nilai_huruf "
                    + "FROM ta_final, ta "
                    + "WHERE ta.id_ta = ta_final.id_ta AND ta.status_ta = '1' AND id_final = ? ";
            
    
                prepare = koneksi.prepareStatement(sql);

                prepare.setInt(1, sidang.getIdSidang());

                result = prepare.executeQuery();

                if(result.next()){

                    sidang.setIdSidang(result.getInt("id_final"));
                    sidang.setTglSidang(result.getString("final_tgl_sidang"));
                    sidang.setWktSidang(result.getString("final_waktu_sidang"));
                    sidang.setTempatSidang(result.getString("final_tempat_sidang"));
                    sidang.setPenguji1(result.getString("final_penguji1"));
                    sidang.setPenguji2(result.getString("final_penguji2"));
//                    sidang.setTglDiterima(result.getString("final_tgl_diterima"));
                    sidang.setStatusSidang(result.getString("status_sidang_final"));
                    sidang.setNilaiPembimbing1(result.getInt("nilai_pembimbing1"));
                    sidang.setNilaiPembimbing2(result.getInt("nilai_pembimbing2"));
                    sidang.setNilaiPenguji1(result.getInt("nilai_penguji1"));
                    sidang.setNilaiPenguji2(result.getInt("nilai_penguji2"));
                    sidang.setNilaiAngka(result.getInt("nilai_angka"));
                    sidang.setNilaiHuruf(result.getString("nilai_huruf"));
                }
            }
            return sidang;
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){

                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return sidang;
    }
    
    //Metod untuk melakukan rekam berita acara 
    public boolean RekamBeritaAcara(Sidang sidang){
        
        boolean hasil = true;
        
        PreparedStatement prepare = null;
        
        String jenisSidang = sidang.getJenisSidang();
        String sql = null;
            
            if("SIDANG PROPOSAL".equals(jenisSidang)){
                
                try{
                        sql = "update ta_proposal set status_sidang_proposal = ? where id_proposal = ? ";

                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, sidang.getStatusSidang());
                        prepare.setInt(2, sidang.getIdSidang());

                        int status = prepare.executeUpdate();
                        if(status > 0){
                            hasil = true;
                        }else{
                            hasil = false;
                        }
                    
                    
                }catch(SQLException ex){
                    hasil = false;
                    System.out.println(ex.getMessage());                    
                }finally{
                    if(prepare != null){
                        try{
                            prepare.close();
                        }catch(SQLException ex){                   
                        }
                    }   
                }  
            }else{
                
                try{
                    
                        sql = "update ta_final set status_sidang_final = ? , nilai_pembimbing1 = ?, nilai_pembimbing2 = ?, "
                            + "nilai_penguji1 = ?, nilai_penguji2 = ?, nilai_angka = ?, nilai_huruf = ? "
                            + "where id_final = ? ";

                        prepare = koneksi.prepareStatement(sql);

                        prepare.setString(1, sidang.getStatusSidang());
                        prepare.setInt(2, sidang.getNilaiPembimbing1());
                        prepare.setInt(3, sidang.getNilaiPembimbing2());
                        prepare.setInt(4, sidang.getNilaiPenguji1());
                        prepare.setInt(5, sidang.getNilaiPenguji2());
                        prepare.setInt(6, sidang.getNilaiAngka());
                        prepare.setString(7, sidang.getNilaiHuruf());
                        prepare.setInt(8, sidang.getIdSidang());

                        int status = prepare.executeUpdate();
                        if(status > 0){
                            hasil = true;
                        }else{
                            hasil = false;
                        }
//                    }
                }catch(SQLException ex){
                    hasil = false;
                    System.out.println(ex.getMessage());                    
                }finally{
                    if(prepare != null){
                        try{
                            prepare.close();
                        }catch(SQLException ex){                   
                        }
                    }   
                }
            }
            return hasil;
    }
    
    public List<Sidang> RekapBeritaAcara(Sidang sidang, String periode){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Sidang> listRekapBeritaAcara = new ArrayList<Sidang>();
        String jenisSidang = sidang.getJenisSidang();
        String status = sidang.getStatusSidang();
        
        String sql = null;
        
        
        if("SIDANG PROPOSAL".equals(jenisSidang)){
            String[] token = periode.split(",");
            String periodeAwal = token[0];
            String periodeAkhir = token[1];
            System.out.println(periodeAwal);
            System.out.println(periodeAkhir);
            try{
                sql = "SELECT id_proposal, ta_proposal.id_ta, ta.id_user, ta.nama_lab, proposal_tgl_sidang, "
                    + "proposal_penguji1, proposal_penguji2, status_sidang_proposal, ta.status_ta "
                    + "FROM ta_proposal, ta "
                    + "WHERE ta_proposal.status_daftar_proposal = 'OK' AND proposal_tgl_sidang is not null "
                    + "AND ta.id_ta = ta_proposal.id_ta "
                    + "AND status_sidang_proposal = ? AND ta_proposal.proposal_tgl_sidang >= ? AND ta_proposal.proposal_tgl_sidang <= ? "
                    + "ORDER BY ta.id_user ";

                /*kondisi cadangan
                    * 1. AND ta_proposal.status_sidang_proposal <> 'DITERIMA' 
                */
                prepare = koneksi.prepareStatement(sql);

                prepare.setString(1, status);
                prepare.setString(2, periodeAwal);
                prepare.setString(3, periodeAkhir);
                result = prepare.executeQuery();

                while(result.next()){

                    User user = new User();
                    user.setIdUser(result.getString("id_user"));

                    TA ta = new TA();
                    ta.setUser(user);
                    ta.setIdTA(result.getInt("id_ta"));
                    ta.setNamaLab(result.getString("nama_lab"));
                    ta.setStatusTA(result.getString("status_ta"));
                    
                    Sidang sidangta = new Sidang();
                    sidangta.setTa(ta);
                    sidangta.setIdSidang(result.getInt("id_proposal"));
                    sidangta.setTglSidang(result.getString("proposal_tgl_sidang"));
                    sidangta.setPenguji1(result.getString("proposal_penguji1"));
                    sidangta.setPenguji2(result.getString("proposal_penguji2"));
                    sidangta.setStatusSidang(result.getString("status_sidang_proposal"));
                    listRekapBeritaAcara.add(sidangta);
                }
                return listRekapBeritaAcara;
            
            }catch(SQLException ex){          
                System.out.println(ex.getMessage());
                return listRekapBeritaAcara;
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            }
        }else if("SIDANG FINAL".equals(jenisSidang)){
            
            String[] token = periode.split(",");
            String periodeAwal = token[0];
            String periodeAkhir = token[1];
            
            try{
                sql = "SELECT id_final, ta_final.id_ta, ta.id_user, ta.nama_lab, final_tgl_sidang, "
                    + "final_penguji1, final_penguji2, nilai_angka, nilai_huruf, status_sidang_final, ta.status_ta "
                    + "FROM ta_final, ta "
                    + "WHERE ta_final.status_daftar_final = 'OK' AND final_tgl_sidang is not null "
                    + "AND ta.id_ta = ta_final.id_ta "
                    + "AND status_sidang_final = ? AND ta_final.final_tgl_sidang >= ? AND ta_final.final_tgl_sidang <= ? "
                    + "ORDER BY ta.id_user ";

                /*kondisi cadangan
                    * 1. AND ta_proposal.status_sidang_proposal <> 'DITERIMA' 
                */
                prepare = koneksi.prepareStatement(sql);

                prepare.setString(1, status);
                prepare.setString(2, periodeAwal);
                prepare.setString(3, periodeAkhir);
                result = prepare.executeQuery();

                while(result.next()){

                    User user = new User();
                    user.setIdUser(result.getString("id_user"));

                    TA ta = new TA();
                    ta.setUser(user);
                    ta.setIdTA(result.getInt("id_ta"));
                    ta.setNamaLab(result.getString("nama_lab"));
                    ta.setStatusTA(result.getString("status_ta"));

                    Sidang sidangta = new Sidang();
                    sidangta.setTa(ta);
                    sidangta.setIdSidang(result.getInt("id_final"));
                    sidangta.setTglSidang(result.getString("final_tgl_sidang"));
                    sidangta.setPenguji1(result.getString("final_penguji1"));
                    sidangta.setPenguji2(result.getString("final_penguji2"));
                    sidangta.setNilaiAngka(result.getInt("nilai_angka"));
                    sidangta.setNilaiHuruf(result.getString("nilai_huruf"));
                    sidangta.setStatusSidang(result.getString("status_sidang_final"));
                    listRekapBeritaAcara.add(sidangta);
                }
                return listRekapBeritaAcara;
            
            }catch(SQLException ex){          
                System.out.println(ex.getMessage());
                return listRekapBeritaAcara;
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
                if(result != null){
                    try{
                        result.close();
                    }catch(SQLException ex){
                        System.out.println(ex.getMessage());
                    }
                }
            } 
        }
        return listRekapBeritaAcara;
    }
}   
   